/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.bld.model.cliente;

import br.com.bld.util.facade.AbstractDao;
import br.com.bld.util.facade.EntityFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.HashMap;

/**
 *
 * @author Pedro
 */
class ClienteDao extends AbstractDao {

    public ClienteDao(Connection conn, EntityFactory entityFactory) {
        super(conn, entityFactory);
    }

    private ClientePK getPk(ResultSet rs) throws SQLException {

        int i = 0;

        ClientePK resposta = new ClientePK(rs.getLong(++i));

        return resposta;
    }

    private ClienteEntity getEntity(ResultSet rs) throws SQLException {

        ClienteEntity resposta = new ClienteEntity();

        int i = 1;

        rs.getString(++i);
        if (!rs.wasNull()) {
            resposta.setRazaoSocial(rs.getString(i));
        }
        resposta.setNomeFantasia(rs.getString(++i));
        rs.getString(++i);
        if (!rs.wasNull()) {
            resposta.setCnpj(rs.getLong(i));
        }
        rs.getString(++i);
        if (!rs.wasNull()) {
            resposta.setInscricaoEstadual(rs.getString(i));
        }

        resposta.setCategoriaId(rs.getLong(++i));

        rs.getString(++i);
        if (!rs.wasNull()) {
            resposta.setContatoId(rs.getLong(i));
        }

        rs.getString(++i);
        if (!rs.wasNull()) {
            resposta.setPlanoId(rs.getLong(i));
        }

        resposta.setDataCadastro(new Date(rs.getTimestamp(++i).getTime()));

        return resposta;
    }

    public int insert(ClienteEntity entity, ClientePK pk) throws SQLException {

        String sql = createInsert();

        PreparedStatement ps = null;

        try {

            ps = getPreparedStatement(sql);

            int i = 0;

            ps.setLong(++i, pk.getId());

            if (entity.getRazaoSocial() == null || entity.getRazaoSocial().equals("")) {
                ps.setNull(++i, Types.VARCHAR);
            } else {
                ps.setString(++i, entity.getRazaoSocial());
            }

            ps.setString(++i, entity.getNomeFantasia());

            if (entity.getCnpj() == null) {
                ps.setNull(++i, Types.NUMERIC);
            } else {
                ps.setLong(++i, entity.getCnpj());
            }

            if (entity.getInscricaoEstadual() == null || entity.getInscricaoEstadual().equals("")) {
                ps.setNull(++i, Types.VARCHAR);
            } else {
                ps.setString(++i, entity.getInscricaoEstadual());
            }

            ps.setLong(++i, entity.getCategoriaId());

            if (entity.getContatoId() != null && entity.getContatoId() > 0) {
                ps.setLong(++i, entity.getContatoId());
            } else {
                ps.setNull(++i, Types.NUMERIC);
            }

            if (entity.getPlanoId() != null && entity.getPlanoId() > 0) {
                ps.setLong(++i, entity.getPlanoId());
            } else {
                ps.setNull(++i, Types.NUMERIC);
            }

            return executeUpdate(ps);

        } finally {
            close(ps);
        }
    }

    public int update(ClienteEntity entity, ClientePK pk) throws SQLException {

        String sql = createUpdate();

        PreparedStatement ps = null;

        try {

            ps = getPreparedStatement(sql);

            int i = 0;

            if (entity.getRazaoSocial() == null || entity.getRazaoSocial().equals("")) {
                ps.setNull(++i, Types.VARCHAR);
            } else {
                ps.setString(++i, entity.getRazaoSocial());
            }

            ps.setString(++i, entity.getNomeFantasia());

            if (entity.getCnpj() == null) {
                ps.setNull(++i, Types.NUMERIC);
            } else {
                ps.setLong(++i, entity.getCnpj());
            }

            if (entity.getInscricaoEstadual() == null || entity.getInscricaoEstadual().equals("")) {
                ps.setNull(++i, Types.VARCHAR);
            } else {
                ps.setString(++i, entity.getInscricaoEstadual());
            }

            ps.setLong(++i, entity.getCategoriaId());

            if (entity.getContatoId() != null && entity.getContatoId() > 0) {
                ps.setLong(++i, entity.getContatoId());
            } else {
                ps.setNull(++i, Types.NUMERIC);
            }

            if (entity.getPlanoId() != null && entity.getPlanoId() > 0) {
                ps.setLong(++i, entity.getPlanoId());
            } else {
                ps.setNull(++i, Types.NUMERIC);
            }

            ps.setLong(++i, pk.getId());

            return executeUpdate(ps);

        } finally {
            close(ps);
        }
    }

    public int delete(ClientePK pk) throws SQLException {

        String sql = createDelete();

        PreparedStatement ps = null;

        try {

            ps = getPreparedStatement(sql);

            int i = 0;

            ps.setLong(++i, pk.getId());

            return executeUpdate(ps);

        } finally {
            close(ps);
        }
    }

    public HashMap<ClientePK, ClienteEntity> selectAll() throws SQLException {

        HashMap<ClientePK, ClienteEntity> resposta = new HashMap();
        String sql = createSelect();

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            ps = getPreparedStatement(sql);
            rs = getResultSet(ps);

            while (rs.next()) {
                resposta.put(getPk(rs), getEntity(rs));
            }

            return resposta;
        } finally {
            close(ps, rs);
        }
    }

    public ClienteEntity selectByPk(ClientePK pk) throws SQLException {
        ClienteEntity resposta = null;

        String sql = createSelectByPk();

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            ps = getPreparedStatement(sql);

            int i = 0;

            ps.setLong(++i, pk.getId());

            rs = getResultSet(ps);

            if (rs.next()) {
                resposta = getEntity(rs);
            }

            return resposta;
        } finally {
            close(ps, rs);
        }
    }

    public HashMap<ClientePK, ClienteEntity> selectByPage(Integer numrPagina, Integer qtdePorPagina) throws SQLException {

        HashMap<ClientePK, ClienteEntity> resposta = new HashMap();
        String sql = createSelectByPage(numrPagina, qtdePorPagina);

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            ps = getPreparedStatement(sql);
            rs = getResultSet(ps);

            while (rs.next()) {
                resposta.put(getPk(rs), getEntity(rs));
            }

            return resposta;
        } finally {
            close(ps, rs);
        }
    }

    public ClienteEntity selectByNomeFantasia(String nomeFantasia) throws SQLException {
        ClienteEntity resposta = null;

        String[] KEY = {"nome_fantasia"};

        String sql = createSelectByKey(KEY);

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            ps = getPreparedStatement(sql);

            ps.setString(1, nomeFantasia);

            rs = getResultSet(ps);

            if (rs.next()) {
                resposta = getEntity(rs);
            }

            return resposta;

        } finally {
            close(ps, rs);
        }
    }

     public ClientePK selectPkByNomeFantasia(String nomeFantasia) throws SQLException {
        ClientePK resposta = null;

        String[] KEY = {"nome_fantasia"};

        String sql = createSelectByKey(KEY);

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            ps = getPreparedStatement(sql);

            ps.setString(1, nomeFantasia);

            rs = getResultSet(ps);

            if (rs.next()) {
                resposta = getPk(rs);
            }

            return resposta;

        } finally {
            close(ps, rs);
        }
    }
    
    public ClientePK selectPKByEntity(ClienteEntity entity) throws SQLException {

        String sql = createSelectByEntity();

        PreparedStatement ps = null;
        ResultSet rs = null;

        ClientePK resposta = null;

        try {

            ps = getPreparedStatement(sql);

            int i = 0;

            if (entity.getRazaoSocial() == null || entity.getRazaoSocial().equals("")) {
                ps.setNull(++i, Types.VARCHAR);
            } else {
                ps.setString(++i, entity.getRazaoSocial());
            }

            ps.setString(++i, entity.getNomeFantasia());

            if (entity.getCnpj() == null) {
                ps.setNull(++i, Types.NUMERIC);
            } else {
                ps.setLong(++i, entity.getCnpj());
            }

            if (entity.getInscricaoEstadual() == null || entity.getInscricaoEstadual().equals("")) {
                ps.setNull(++i, Types.VARCHAR);
            } else {
                ps.setString(++i, entity.getInscricaoEstadual());
            }

            ps.setLong(++i, entity.getCategoriaId());

            if (entity.getContatoId() != null && entity.getContatoId() > 0) {
                ps.setLong(++i, entity.getContatoId());
            } else {
                ps.setNull(++i, Types.NUMERIC);
            }

            if (entity.getPlanoId() != null && entity.getPlanoId() > 0) {
                ps.setLong(++i, entity.getPlanoId());
            } else {
                ps.setNull(++i, Types.NUMERIC);
            }

            rs = getResultSet(ps);

            if (rs.next()) {
                resposta = getPk(rs);
            }

            return resposta;

        } finally {
            close(ps, rs);
        }
    }
}
